﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using AutoMapper;
using DTO;

namespace DAO
{
    public class ChiTietDoanhThuThang_DAO
    {
        public static ChiTietDoanhThuThang_DTO CreateDTO(ChiTietDoanhThuThang chitiet)
        {
            ChiTietDoanhThuThang_DTO DTO = new ChiTietDoanhThuThang_DTO();
            Mapper.CreateMap<ChiTietDoanhThuThang, ChiTietDoanhThuThang_DTO>();
            DTO = Mapper.Map<ChiTietDoanhThuThang, ChiTietDoanhThuThang_DTO>(chitiet);
            return DTO;
        }

        public float DoanhThuBanVe(string maChuyenBay)
        {
            float Kq = 0;
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = (from ve in context.VeChuyenBays
                             where ve.MaChuyenBay == maChuyenBay
                             select ve.GiaTien).Sum();
                Kq = (float)query;
            }
            catch { }
            return Kq;
        }
        public float DoanhThuDatVe(string maChuyenBay)
        {
            float Kq = 0;
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = (from ve in context.PhieuDatChos
                             where ve.MaChuyenBay == maChuyenBay
                             select ve.GiaTien).Sum();
                Kq = (float)query;
            }
            catch { }
            return Kq;
        }
        public int SoGheTrong(string maChuyenBay)
        {
            int SL = 0;
            QLCBDataContext context = new QLCBDataContext();
            var query = (from tt in context.TinhTrangVes
                         where tt.MaChuyenBay == maChuyenBay
                         select tt.SoGheTrong).Sum();
            SL = (int)query;
            return SL;
        }
        public int SoGheDat(string maChuyenBay)
        {
            int SL = 0;
            QLCBDataContext context = new QLCBDataContext();
            var query = (from tt in context.TinhTrangVes
                         where tt.MaChuyenBay == maChuyenBay
                         select tt.SoGheDat).Sum();
            SL = (int)query;
            return SL;
        }

        public BindingList<ChiTietDoanhThuThang_DTO> GetBaoCaoThangList(int Thang, int Nam)
        {
            BindingList<ChiTietDoanhThuThang_DTO> list = new BindingList<ChiTietDoanhThuThang_DTO>();
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = from cb in context.ChuyenBays
                            where cb.Ngay_Gio.Value.Year == Nam && cb.Ngay_Gio.Value.Month == Thang
                            select new
                            {
                                cb.MaChuyenBay,
                            };
                int mSTT = 1;
                foreach (var x in query)
                {
                    ChiTietDoanhThuThang_DTO DTO = new ChiTietDoanhThuThang_DTO();
                    DTO.STT = mSTT;
                    DTO.MaChuyenBay = x.MaChuyenBay;
                    DTO.TongSoVe = SoGheTrong(x.MaChuyenBay) + SoGheDat(x.MaChuyenBay);
                    DTO.SoVe = SoGheDat(DTO.MaChuyenBay);
                    DTO.TyLe = (DTO.SoVe * 100) / DTO.TongSoVe;
                    float dtbv = DoanhThuBanVe(DTO.MaChuyenBay);
                    float dtdv = DoanhThuDatVe(DTO.MaChuyenBay);

                    DTO.DoanhThu = dtbv + dtdv;
                    list.Add(DTO);
                    mSTT++;
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return list;
        }

        public BindingList<ChiTietDoanhThuThang_DTO> GetBaoCaoNamList(int Nam)
        {
            BindingList<ChiTietDoanhThuThang_DTO> list = new BindingList<ChiTietDoanhThuThang_DTO>();
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = from cb in context.ChuyenBays
                            where cb.Ngay_Gio.Value.Year == Nam
                            select new
                            {
                                cb.MaChuyenBay,
                            };
                int mSTT = 1;
                foreach (var x in query)
                {
                    ChiTietDoanhThuThang_DTO DTO = new ChiTietDoanhThuThang_DTO();
                    DTO.STT = mSTT;
                    DTO.MaChuyenBay = x.MaChuyenBay;
                    DTO.TongSoVe = SoGheTrong(x.MaChuyenBay) + SoGheDat(x.MaChuyenBay);
                    DTO.SoVe = SoGheDat(DTO.MaChuyenBay);
                    DTO.TyLe = (DTO.SoVe * 100) / DTO.TongSoVe;
                    float dtbv = DoanhThuBanVe(DTO.MaChuyenBay);
                    float dtdv = DoanhThuDatVe(DTO.MaChuyenBay);

                    DTO.DoanhThu = dtbv + dtdv;
                    list.Add(DTO);
                    mSTT++;
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return list;
        }

        public BindingList<ChiTietDoanhThuThang_DTO> GetChiTietList()
        {
            BindingList<ChiTietDoanhThuThang_DTO> list = new BindingList<ChiTietDoanhThuThang_DTO>();
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = from chitiet in context.ChiTietDoanhThuThangs select chitiet;
                foreach (var cb in query)
                {
                    ChiTietDoanhThuThang_DTO chuyenbayDTO = CreateDTO(cb);
                    list.Add(chuyenbayDTO);
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return list;
        }

        public BindingList<ChiTietDoanhThuThang_DTO> GetChiTietTheoThangList(string MaThang)
        {
            BindingList<ChiTietDoanhThuThang_DTO> list = new BindingList<ChiTietDoanhThuThang_DTO>();
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = from chitiet in context.ChiTietDoanhThuThangs 
                                 where chitiet.MaDoanhThuThang == MaThang
                                 select chitiet;
                int mSTT = 1;
                foreach (var cb in query)
                {
                    ChiTietDoanhThuThang_DTO DTO = CreateDTO(cb);
                    DTO.STT = mSTT;
                    list.Add(DTO);
                    mSTT++;
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return list;
        }

        public static string MaChiTietMax()
        {
            QLCBDataContext context = new QLCBDataContext();
            string query = (from cb in context.ChiTietDoanhThuThangs select cb.MaChiTietDoanhThuThang).Max();
            return query;
        }

        public void InsertChiTiet(ChiTietDoanhThuThang_DTO DTO)
        {
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                ChiTietDoanhThuThang chitiet = new ChiTietDoanhThuThang();
                chitiet.MaChiTietDoanhThuThang = DTO.MaChiTietDoanhThu;
                chitiet.MaDoanhThuThang = DTO.MaDoanhThuThang;
                chitiet.MaChuyenBay = DTO.MaChuyenBay;
                chitiet.SoVe = DTO.SoVe;
                chitiet.TyLe = DTO.TyLe;
                chitiet.DoanhThu = DTO.DoanhThu;
                context.ChiTietDoanhThuThangs.InsertOnSubmit(chitiet);
                context.SubmitChanges();
            }
            catch
            { }
        }

        public void UpdateChiTiet(ChiTietDoanhThuThang_DTO DTO)
        {
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                ChiTietDoanhThuThang chitiet = new ChiTietDoanhThuThang();
                chitiet = context.ChiTietDoanhThuThangs.Single(ct => ct.MaChiTietDoanhThuThang == DTO.MaChiTietDoanhThu);
                chitiet.MaChiTietDoanhThuThang = DTO.MaChiTietDoanhThu;
                chitiet.MaDoanhThuThang = DTO.MaDoanhThuThang;
                chitiet.MaChuyenBay = DTO.MaChuyenBay;
                chitiet.SoVe = DTO.SoVe;
                chitiet.TyLe = DTO.TyLe;
                chitiet.DoanhThu = DTO.DoanhThu;
                context.SubmitChanges();
            }
            catch
            { }
        }

        public void DeleteChiTiet(ChiTietDoanhThuThang_DTO DTO)
        {
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                ChiTietDoanhThuThang chitiet = context.ChiTietDoanhThuThangs.Single(ct => ct.MaChiTietDoanhThuThang == DTO.MaChiTietDoanhThu);
                context.ChiTietDoanhThuThangs.DeleteOnSubmit(chitiet);
                context.SubmitChanges();
            }
            catch
            { }
        }
    }
}
